package dao;

import bean.Book;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class BookDao {
    /**
     * @param book 一个图书对象
     * @return 是否插入成功
     */
    public boolean insertBook(Book book) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
// 获取数据库连接
            conn = JDBCUtils.getConnection();
// 根据sql 模板创建对应的预编译对象并将传入的形参赋值模板，替换掉sql 模板中“？”
            String sql = "insert into mvc.book (bookName, author, press, isbn, category, price ) values (?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book.getBookName());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getPress());
            pstmt.setString(4, book.getIsbn());
            pstmt.setString(5, book.getCategory());
            pstmt.setFloat(6, book.getPrice());
// 执行插入数据操作
            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
        } catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block

            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.release(pstmt, conn);
            } catch (SQLException e) {
// TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * @return 所有书籍信息
     */
    public List<Book> findAll() throws SQLException {
        List<Book> bookList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement("select * from mvc.book");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Book book = new Book(
                        rs.getInt("id"),
                        rs.getString("BookName"),
                        rs.getString("author"),
                        rs.getString("press"),
                        rs.getString("isbn"),
                        rs.getString("category"),
                        rs.getFloat("price")
                );
                bookList.add(book);

            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return bookList;
    }

    public Book findBook(int id) {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from mvc.book where id=" + id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                return new Book(
                        rs.getInt("id"),
                        rs.getString("BookName"),
                        rs.getString("author"),
                        rs.getString("press"),
                        rs.getString("isbn"),
                        rs.getString("category"),
                        rs.getFloat("price")
                );

            }

        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public boolean deleteBook(int id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement("delete from mvc.book where id="+id);
            int num = ps.executeUpdate();
            if (num > 0) {
                return true;
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

}
